package zy.dao.home.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.home.HomeDAO;
import zy.entity.sell.day.T_Sell_Day;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class HomeDAOImpl extends BaseDaoImpl implements HomeDAO{
	
	@Override
	public List<String> listAllShop(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT sp_code");
		sql.append(" FROM t_base_shop t");
		sql.append(getShopSQL(shop_type, 0));
		sql.append(" WHERE 1 = 1");
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}
	
	@Override
	public Map<String, Object> loadBusinessInfo(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		final Map<String, Object> resultMap = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		//查询仓库
		sql.append(" SELECT dp_code ");
		sql.append(" FROM t_base_depot dp");
		sql.append(" JOIN t_base_shop sp ON dp_shop_code = sp_code AND dp.companyid = sp.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" AND dp.companyid = :companyid");
		params.put("allDepot", namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class));
		
		//库存数量、库存成本
		sql.setLength(0);
		sql.append(" SELECT SUM(sd_amount) AS sd_amount,");
		if(CommonUtil.ONE.equals(shop_type)){
			sql.append(" SUM(sd_amount*pd_cost_price) AS sd_costmoney");
		}else {
			sql.append(" SUM(f_GetProductCostPrice(pd_code,:shop_code,pd.companyid)*sd_amount) AS sd_costmoney");
		}
		sql.append(" FROM ");
		sql.append(" (SELECT sd_pd_code,SUM(sd_amount) AS sd_amount,companyid");
		sql.append(" FROM t_stock_data");
		sql.append(" WHERE 1=1");
		sql.append(" AND sd_dp_code IN (:allDepot)");
		sql.append(" AND companyid = :companyid");
		sql.append(" GROUP BY sd_pd_code");
		sql.append(" )t ");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND pd.companyid = :companyid");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				resultMap.put("sd_amount", rs.getInt("sd_amount"));
				resultMap.put("sd_costmoney", rs.getDouble("sd_costmoney"));
				return null;
			};
		});
		//银行存款、现金、供应商欠款、客户欠款
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.setLength(0);
			sql.append(" SELECT IFNULL(SUM(ba_balance),0) AS ba_balance");
			sql.append(" FROM t_money_bank");
			sql.append(" WHERE 1=1");
			sql.append(" AND ba_shop_code = :shop_code");
			sql.append(" AND INSTR(ba_name,'现金') = 0");
			sql.append(" AND companyid = :companyid");
			resultMap.put("ba_balance", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Double.class));
			sql.setLength(0);
			sql.append(" SELECT IFNULL(SUM(ba_balance),0) AS ba_balance");
			sql.append(" FROM t_money_bank");
			sql.append(" WHERE 1=1");
			sql.append(" AND ba_shop_code = :shop_code");
			sql.append(" AND INSTR(ba_name,'现金') > 0");
			sql.append(" AND companyid = :companyid");
			resultMap.put("ba_balance_cash", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Double.class));
			sql.setLength(0);
			sql.append(" SELECT IFNULL(SUM(sp_payable-sp_payabled-sp_prepay),0) AS actualDebt");
			sql.append(" FROM t_buy_supply");
			sql.append(" WHERE 1=1");
			sql.append(" AND companyid = :companyid");
			resultMap.put("supply_debt", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Double.class));
			sql.setLength(0);
			sql.append(" SELECT IFNULL(SUM(ci_receivable-ci_received-ci_prepay),0) AS actualDebt ");
			sql.append(" FROM t_batch_client");
			sql.append(" WHERE 1=1");
			sql.append(" AND ci_sp_code = :shop_code");
			sql.append(" AND companyid = :companyid");
			resultMap.put("client_debt", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Double.class));
		}else {//费用金额、其他收入
			sql.setLength(0);
			sql.append(" SELECT IFNULL(SUM(epl_money),0) AS money");
			sql.append(" FROM t_money_expense t");
			sql.append(" JOIN t_money_expenselist epl ON epl_number = ep_number AND epl.companyid = t.companyid");
			sql.append(" WHERE 1=1");
			sql.append(" AND MONTH(epl_sharedate) = MONTH(CURDATE())");
			sql.append(" AND ep_ar_state = 1");
			sql.append(" AND ep_shop_code = :shop_code");
			sql.append(" AND t.companyid = :companyid");
			resultMap.put("expense", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Double.class));
			sql.setLength(0);
			sql.append(" SELECT IFNULL(SUM(ic_money),0) AS money");
			sql.append(" FROM t_money_income t");
			sql.append(" WHERE 1=1");
			sql.append(" AND ic_ar_state = 1");
			sql.append(" AND ic_shop_code = :shop_code");
			sql.append(" AND MONTH(ic_sysdate) = MONTH(CURDATE())");
			sql.append(" AND t.companyid = :companyid");
			resultMap.put("income", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Double.class));
		}
		return resultMap;
	}
	
	@Override
	public Map<String, Object> loadHomeInfo(Map<String, Object> params) {
		final Map<String, Object> resultMap = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		//进店量
		sql.append(" SELECT SUM(da_come) AS comeAmount,SUM(da_receive) AS receiveAmount,SUM(da_try) AS tryAmount");
		sql.append(" FROM t_sell_day");
		sql.append(" WHERE 1=1");
		sql.append(" AND da_date >= :begindate");
		sql.append(" AND da_date <= :enddate");
		sql.append(" AND da_shop_code IN(:shopCodes)");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				resultMap.put("comeAmount", rs.getInt("comeAmount"));
				resultMap.put("receiveAmount", rs.getInt("receiveAmount"));
				resultMap.put("tryAmount", rs.getInt("tryAmount"));
				return null;
			};
		});
		//成交单数、退货单数
		sql.setLength(0);
		final List<Integer> dealCount_Sell = new ArrayList<Integer>();
		final List<Integer> dealCount_Back = new ArrayList<Integer>();
		sql.append(" SELECT shl_state,COUNT(DISTINCT shl_number) AS dealCount");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_shop_code IN(:shopCodes)");
		sql.append(" AND shl_date >= :begindate");
		sql.append(" AND shl_date <= :enddate");
		sql.append(" AND shl_state IN(0,1)");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY shl_state");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				if("0".equals(rs.getString("shl_state"))){
					dealCount_Sell.add(rs.getInt("dealCount"));
				}else {
					dealCount_Back.add(rs.getInt("dealCount"));
				}
				return null;
			};
		});
		int dealCount = 0;
		int returnDealCount = 0;
		if (dealCount_Sell.size() > 0) {
			dealCount += dealCount_Sell.get(0);
		}
		if (dealCount_Back.size() > 0) {
			dealCount -= dealCount_Back.get(0);
			returnDealCount = dealCount_Back.get(0);
		}
		
		resultMap.put("dealCount", dealCount);
		resultMap.put("returnDealCount", returnDealCount);
		//销售金额
		sql.setLength(0);
		sql.append(" SELECT IFNULL(SUM(sh_amount),0) AS sellAmount,IFNULL(SUM(sh_money),0) AS sellMoney,");
		sql.append(" IFNULL(SUM(sh_money-sh_cost_money),0) AS sellProfits");
		sql.append(" FROM t_sell_shop t");
		sql.append(" WHERE 1=1");
		sql.append(" AND sh_shop_code IN(:shopCodes)");
		sql.append(" AND sh_date >= :begindate");
		sql.append(" AND sh_date <= :enddate");
		sql.append(" AND t.companyid = :companyid");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				resultMap.put("sellAmount", rs.getInt("sellAmount"));
				resultMap.put("sellMoney", rs.getDouble("sellMoney"));
				resultMap.put("sellProfits", rs.getDouble("sellProfits"));
				return null;
			};
		});
		//连带率=销售数量/成交单数
		int sellAmount = 0;
		if(resultMap.containsKey("sellAmount")){
			sellAmount = Integer.parseInt(resultMap.get("sellAmount").toString());
		}
		if(dealCount != 0){
			resultMap.put("jointRate", String.format("%.1f", sellAmount/(double)dealCount));
		}else {
			resultMap.put("jointRate", 0);
		}
		//客单价=销售额/成交单数
		double sellMoney = 0d;
		if(resultMap.containsKey("sellMoney")){
			sellMoney = Double.parseDouble(resultMap.get("sellMoney").toString());
		}
		if(dealCount != 0){
			resultMap.put("avgSellPrice", String.format("%.1f", sellMoney/dealCount));
		}else {
			resultMap.put("avgSellPrice", 0);
		}
		//新增会员
		sql.setLength(0);
		sql.append(" SELECT COUNT(1) AS totalCount");
		sql.append(" FROM t_vip_member t");
		sql.append(" WHERE 1=1");
		sql.append(" AND vm_shop_code IN(:shopCodes)");
		sql.append(" AND vm_sysdate >= :begindate");
		sql.append(" AND vm_sysdate <= :enddate");
		sql.append(" AND t.companyid = :companyid");
		resultMap.put("newVipCount", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
		//发放储值卡
		sql.setLength(0);
		sql.append(" SELECT COUNT(1)");
		sql.append(" FROM t_sell_card t");
		sql.append(" WHERE 1=1");
		sql.append(" AND cd_shop_code IN(:shopCodes)");
		sql.append(" AND cd_grantdate >= :begindate");
		sql.append(" AND cd_grantdate <= :enddate");
		sql.append(" AND t.companyid = :companyid");
		resultMap.put("newCardCount", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
		
		return resultMap;
	}
	
	@Override
	public Map<String, Object> statSellByDay(Map<String, Object> params) {
		final Map<String, Object> resultMap = new HashMap<String, Object>();
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT DAY(shl_date) day,IFNULL(SUM(shl_money),0) AS sellMoney");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" JOIN t_base_shop sp ON sp_code = shl_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" AND shl_date >= :begindate");
		sql.append(" AND shl_date <= :enddate");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY DAY(shl_date)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				resultMap.put(rs.getString("day"), rs.getDouble("sellMoney"));
				return null;
			};
		});
		return resultMap;
	}

	@Override
	public Map<String, Object> statDayKpi(Map<String, Object> params) {
		final Map<String, Object> resultMap = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		//进店量
		sql.setLength(0);
		sql.append(" SELECT SUM(da_come) AS comeAmount,SUM(da_receive) AS receiveAmount,SUM(da_try) AS tryAmount");
		sql.append(" FROM t_sell_day");
		sql.append(" WHERE 1=1");
		sql.append(" AND da_date = :date");
		sql.append(" AND da_shop_code IN(:shopCodes)");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				resultMap.put("comeAmount", rs.getInt("comeAmount"));
				resultMap.put("receiveAmount", rs.getInt("receiveAmount"));
				resultMap.put("tryAmount", rs.getInt("tryAmount"));
				return null;
			};
		});
		//成交单数、退货单数
		sql.setLength(0);
		final List<Integer> dealCount_Sell = new ArrayList<Integer>();
		final List<Integer> dealCount_Back = new ArrayList<Integer>();
		sql.append(" SELECT shl_state,COUNT(DISTINCT shl_number) AS dealCount");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_shop_code IN(:shopCodes)");
		sql.append(" AND shl_date = :date");
		sql.append(" AND shl_state IN(0,1)");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY shl_state");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				if("0".equals(rs.getString("shl_state"))){
					dealCount_Sell.add(rs.getInt("dealCount"));
				}else {
					dealCount_Back.add(rs.getInt("dealCount"));
				}
				return null;
			};
		});
		int dealCount = 0;
		int returnDealCount = 0;
		if (dealCount_Sell.size() > 0) {
			dealCount += dealCount_Sell.get(0);
		}
		if (dealCount_Back.size() > 0) {
			dealCount -= dealCount_Back.get(0);
			returnDealCount = dealCount_Back.get(0);
		}
		resultMap.put("dealCount", dealCount);
		resultMap.put("returnDealCount", returnDealCount);
		//成交会员个数
		sql.setLength(0);
		sql.append(" SELECT COUNT(DISTINCT shl_vip_code) AS dealVipCount");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_shop_code IN(:shopCodes)");
		sql.append(" AND shl_date = :date");
		sql.append(" AND shl_state = 0");
		sql.append(" AND shl_vip_code != ''");
		sql.append(" AND shl_vip_code IS NOT NULL");
		sql.append(" AND t.companyid = :companyid");
		resultMap.put("dealVipCount", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
		//会员总数
		sql.setLength(0);
		sql.append(" SELECT COUNT(1) AS allVipCount");
		sql.append(" FROM t_vip_member t");
		sql.append(" WHERE 1=1");
		sql.append(" AND vm_shop_code IN(:shopCodes)");
		sql.append(" AND t.companyid = :companyid");
		resultMap.put("allVipCount", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
		
		//销售金额、销售数量
		sql.setLength(0);
		sql.append(" SELECT IFNULL(SUM(shl_amount),0) AS sellAmount,");
		sql.append(" IFNULL(SUM(shl_money),0) AS sellMoney,");
		sql.append(" IFNULL(SUM(shl_cost_price*shl_amount),0) AS costMoney,");
		sql.append(" IFNULL(SUM(shl_sell_price*shl_amount),0) AS retailMoney");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_shop_code IN(:shopCodes)");
		sql.append(" AND shl_date = :date");
		sql.append(" AND t.companyid = :companyid");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				resultMap.put("sellAmount", rs.getInt("sellAmount"));
				resultMap.put("sellMoney", rs.getDouble("sellMoney"));
				resultMap.put("costMoney", rs.getDouble("costMoney"));
				resultMap.put("retailMoney", rs.getDouble("retailMoney"));
				return null;
			};
		});
		//此周销售
		sql.setLength(0);
		sql.append(" SELECT IFNULL(SUM(sh_money),0)");
		sql.append(" FROM t_sell_shop t");
		sql.append(" WHERE 1=1");
		sql.append(" AND sh_shop_code IN(:shopCodes)");
		sql.append(" AND sh_date >= :weekStart");
		sql.append(" AND sh_date <= :weekEnd");
		sql.append(" AND t.companyid = :companyid");
		resultMap.put("week_sellMoney", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Double.class));
		
		//当天计划金额
		sql.setLength(0);
		sql.append(" SELECT IFNULL(SUM(mpd_sell_money_plan),0)");
		sql.append(" FROM t_shop_monthplan t");
		sql.append(" JOIN t_shop_monthplan_day mpd ON mp_number = mpd_number AND t.companyid = mpd.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND mp_ar_state = 1");
		sql.append(" AND mp_shop_code IN(:shopCodes)");
		sql.append(" AND DATE_FORMAT(CONCAT(mpd_year,'-',mpd_month,'-',mpd_day), '%Y-%m-%d') = :date");
		sql.append(" AND t.companyid = :companyid");
		resultMap.put("day_planMoney", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Double.class));
		//此周计划金额
		sql.setLength(0);
		sql.append(" SELECT IFNULL(SUM(mpd_sell_money_plan),0)");
		sql.append(" FROM t_shop_monthplan t");
		sql.append(" JOIN t_shop_monthplan_day mpd ON mp_number = mpd_number AND t.companyid = mpd.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND mp_ar_state = 1");
		sql.append(" AND mp_shop_code IN(:shopCodes)");
		sql.append(" AND DATE_FORMAT(CONCAT(mpd_year,'-',mpd_month,'-',mpd_day), '%Y-%m-%d') >= :weekStart");
		sql.append(" AND DATE_FORMAT(CONCAT(mpd_year,'-',mpd_month,'-',mpd_day), '%Y-%m-%d') <= :weekEnd");
		sql.append(" AND t.companyid = :companyid");
		resultMap.put("week_planMoney", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Double.class));
		//最高客单价
		sql.setLength(0);
		sql.append(" SELECT IFNULL(MAX(sh_money), 0) AS max_sellMoney,");
		sql.append(" IFNULL(SUM(sh_cash),0) AS sh_cash,");
		sql.append(" IFNULL(SUM(sh_cd_money),0) AS sh_cd_money,");
		sql.append(" IFNULL(SUM(sh_vc_money),0) AS sh_vc_money,");
		sql.append(" IFNULL(SUM(sh_ec_money),0) AS sh_ec_money,");
		sql.append(" IFNULL(SUM(sh_bank_money),0) AS sh_bank_money,");
		sql.append(" IFNULL(SUM(sh_mall_money),0) AS sh_mall_money,");
		sql.append(" IFNULL(SUM(sh_lost_money),0) AS sh_lost_money,");
		sql.append(" IFNULL(SUM(sh_wx_money),0) AS sh_wx_money,");
		sql.append(" IFNULL(SUM(sh_ali_money),0) AS sh_ali_money");
		sql.append(" FROM t_sell_shop t");
		sql.append(" WHERE 1=1");
		sql.append(" AND sh_shop_code IN(:shopCodes)");
		sql.append(" AND sh_date = :date");
		sql.append(" AND t.companyid = :companyid");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				resultMap.put("max_sellMoney", rs.getDouble("max_sellMoney"));
				resultMap.put("sh_cash", rs.getDouble("sh_cash"));
				resultMap.put("sh_cd_money", rs.getDouble("sh_cd_money"));
				resultMap.put("sh_vc_money", rs.getDouble("sh_vc_money"));
				resultMap.put("sh_ec_money", rs.getDouble("sh_ec_money"));
				resultMap.put("sh_bank_money", rs.getDouble("sh_bank_money"));
				resultMap.put("sh_mall_money", rs.getDouble("sh_mall_money"));
				resultMap.put("sh_lost_money", rs.getDouble("sh_lost_money"));
				resultMap.put("sh_wx_money", rs.getDouble("sh_wx_money"));
				resultMap.put("sh_ali_money", rs.getDouble("sh_ali_money"));
				return null;
			};
		});
		//新增会员
		sql.setLength(0);
		sql.append(" SELECT COUNT(1) AS totalCount");
		sql.append(" FROM t_vip_member t");
		sql.append(" WHERE 1=1");
		sql.append(" AND vm_shop_code IN(:shopCodes)");
		sql.append(" AND DATE(vm_sysdate) = :date");
		sql.append(" AND t.companyid = :companyid");
		resultMap.put("newVipCount", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
		//会员消费
		sql.setLength(0);
		sql.append(" SELECT IFNULL(SUM(shl_money),0) AS sellMoney_vip");
		sql.append(" FROM t_sell_shoplist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND shl_shop_code IN(:shopCodes)");
		sql.append(" AND shl_date = :date");
		sql.append(" AND shl_vip_code != ''");
		sql.append(" AND shl_vip_code IS NOT NULL");
		sql.append(" AND t.companyid = :companyid");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				resultMap.put("sellMoney_vip", rs.getDouble("sellMoney_vip"));
				return null;
			};
		});
		String yesterdayDate = DateUtil.lastToday(params.get("date").toString());
		String lastWeekDate = DateUtil.lastWeekToday(params.get("date").toString());
		String lastYearDate = DateUtil.lastYear(params.get("date").toString()).toString();
		params.put("yesterdayDate", yesterdayDate);
		params.put("lastWeekDate", lastWeekDate);
		params.put("lastYearDate", lastYearDate);
		//环比昨日
		sql.setLength(0);
		sql.append(" SELECT IFNULL(SUM(sh_money),0) AS HuanBiYesterday");
		sql.append(" FROM t_sell_shop t");
		sql.append(" WHERE 1=1");
		sql.append(" AND sh_shop_code IN(:shopCodes)");
		sql.append(" AND sh_date = :yesterdayDate");
		sql.append(" AND t.companyid = :companyid");
		resultMap.put("HuanBiYesterday", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Double.class));
		//环比上周几
		sql.setLength(0);
		sql.append(" SELECT IFNULL(SUM(sh_money),0) AS HuanBiLastWeek");
		sql.append(" FROM t_sell_shop t");
		sql.append(" WHERE 1=1");
		sql.append(" AND sh_shop_code IN(:shopCodes)");
		sql.append(" AND sh_date = :lastWeekDate");
		sql.append(" AND t.companyid = :companyid");
		resultMap.put("HuanBiLastWeek", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Double.class));
		//同比去年
		sql.setLength(0);
		sql.append(" SELECT IFNULL(SUM(sh_money),0) AS TongBiLastYear");
		sql.append(" FROM t_sell_shop t");
		sql.append(" WHERE 1=1");
		sql.append(" AND sh_shop_code IN(:shopCodes)");
		sql.append(" AND sh_date = :lastYearDate");
		sql.append(" AND t.companyid = :companyid");
		resultMap.put("TongBiLastYear", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Double.class));
		//发放储值卡
		sql.setLength(0);
		sql.append(" SELECT COUNT(1)");
		sql.append(" FROM t_sell_card t");
		sql.append(" WHERE 1=1");
		sql.append(" AND cd_shop_code IN(:shopCodes)");
		sql.append(" AND cd_grantdate = :date");
		sql.append(" AND t.companyid = :companyid");
		resultMap.put("cardCount", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
		//发放代金券
		sql.setLength(0);
		sql.append(" SELECT COUNT(1)");
		sql.append(" FROM t_sell_voucher t");
		sql.append(" WHERE 1=1");
		sql.append(" AND vc_shop_code IN(:shopCodes)");
		sql.append(" AND vc_grantdate = :date");
		sql.append(" AND t.companyid = :companyid");
		resultMap.put("voucherCount", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
		//优惠券
		sql.setLength(0);
		sql.append(" SELECT COUNT(1)");
		sql.append(" FROM t_sell_ecoupon_user t");
		sql.append(" WHERE 1=1");
		sql.append(" AND ecu_shop_code IN(:shopCodes)");
		sql.append(" AND ecu_date = :date");
		sql.append(" AND t.companyid = :companyid");
		resultMap.put("ecouponCount", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
		//前台调出
		sql.setLength(0);
		sql.append(" SELECT IFNULL(SUM(ac_amount),0) AS allocate_amount_out");
		sql.append(" FROM t_sell_allocate t");
		sql.append(" WHERE 1=1");
		sql.append(" AND ac_out_shop IN(:shopCodes)");
		sql.append(" AND ac_state IN(1,2,3)");
		sql.append(" AND ac_date = :date");
		sql.append(" AND t.companyid = :companyid");
		resultMap.put("allocate_amount_out", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
		//前台调入
		sql.setLength(0);
		sql.append(" SELECT IFNULL(SUM(ac_amount),0) AS allocate_amount_in");
		sql.append(" FROM t_sell_allocate t");
		sql.append(" WHERE 1=1");
		sql.append(" AND ac_in_shop IN(:shopCodes)");
		sql.append(" AND ac_state = 2");
		sql.append(" AND ac_date = :date");
		sql.append(" AND t.companyid = :companyid");
		resultMap.put("allocate_amount_in", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
		//会员生日
		sql.setLength(0);
		sql.append(" SELECT IFNULL(SUM(vipCount),0) AS vipBirthday");
		sql.append(" FROM(");
		sql.append(" SELECT COUNT(1) AS vipCount");
		sql.append(" FROM t_vip_member t");
		sql.append(" WHERE 1=1");
		sql.append(" AND vm_birthday_type = 0");
		sql.append(" AND DATE_FORMAT(vm_birthday,'%m-%d') = DATE_FORMAT(:date,'%m-%d')");
		sql.append(" AND vm_shop_code IN(:shopCodes)");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" UNION ALL");
		sql.append(" SELECT COUNT(1) AS vipCount");
		sql.append(" FROM t_vip_member t");
		sql.append(" WHERE 1=1");
		sql.append(" AND vm_birthday_type = 1");
		sql.append(" AND DATE_FORMAT(vm_lunar_birth,'%m-%d') = DATE_FORMAT(:date,'%m-%d')");
		sql.append(" AND vm_shop_code IN(:shopCodes)");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" )temp");
		resultMap.put("vipBirthday", namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
		
		//此日活动
		String weekDay = DateUtil.getWeek(params.get("date").toString());
		if(weekDay.equals("0")){
			weekDay = "7";
		}
		params.put("weekDay", weekDay);
		sql.setLength(0);
		sql.append(" SELECT DISTINCT ss_name ");
		sql.append(" FROM t_shop_sale t");
		sql.append(" JOIN t_shop_sale_shop sss ON sss_ss_code = ss_code AND sss.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND ss_state = 1");
		sql.append(" AND sss_shop_code IN(:shopCodes)");
		sql.append(" AND ss_begin_date <= :date");
		sql.append(" AND ss_end_date >= :date");
		sql.append(" AND INSTR(ss_week,:weekDay) > 0");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY ss_id DESC");
		resultMap.put("sales", namedParameterJdbcTemplate.queryForList(sql.toString(), params));
		//同期活动
		weekDay = DateUtil.getWeek(lastYearDate);
		if(weekDay.equals("0")){
			weekDay = "7";
		}
		params.put("weekDay", weekDay);
		sql.setLength(0);
		sql.append(" SELECT DISTINCT ss_name ");
		sql.append(" FROM t_shop_sale t");
		sql.append(" JOIN t_shop_sale_shop sss ON sss_ss_code = ss_code AND sss.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND ss_state = 1");
		sql.append(" AND sss_shop_code IN(:shopCodes)");
		sql.append(" AND ss_begin_date <= :lastYearDate");
		sql.append(" AND ss_end_date >= :lastYearDate");
		sql.append(" AND INSTR(ss_week,:weekDay) > 0");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY ss_id DESC");
		resultMap.put("sales_last", namedParameterJdbcTemplate.queryForList(sql.toString(), params));
		return resultMap;
	}
	
	@Override
	public Map<String, T_Sell_Day> statByHour(Map<String, Object> params) {
		final Map<String, T_Sell_Day> resultMap = new HashMap<String, T_Sell_Day>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT HOUR(sh_sysdate) AS hour,SUM(sh_money) AS money");
		sql.append(" FROM t_sell_shop t");
		sql.append(" WHERE 1=1");
		sql.append(" AND sh_shop_code IN(:shopCodes)");
		sql.append(" AND sh_date = :date");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY HOUR(sh_sysdate)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				T_Sell_Day sell_Day = new T_Sell_Day();
				sell_Day.setSh_money(rs.getDouble("money"));
				sell_Day.setDa_come(0);
				resultMap.put(rs.getString("hour"), sell_Day);
				return null;
			};
		});
		sql.setLength(0);
		sql.append(" SELECT HOUR(re_date) AS hour,COUNT(1) AS da_come");
		sql.append(" FROM t_sell_receive t");
		sql.append(" WHERE 1=1");
		sql.append(" AND re_type = 0");
		sql.append(" AND DATE_FORMAT(re_date, '%Y-%m-%d') = :date");
		sql.append(" AND re_shop_code IN(:shopCodes)");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY HOUR(re_date)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				if(resultMap.containsKey(rs.getString("hour"))){
					resultMap.get(rs.getString("hour")).setDa_come(rs.getInt("da_come"));
				}else {
					T_Sell_Day sell_Day = new T_Sell_Day();
					sell_Day.setSh_money(0d);
					sell_Day.setDa_come(rs.getInt("da_come"));
					resultMap.put(rs.getString("hour"), sell_Day);
				}
				return null;
			};
		});
		return resultMap;
	}
	
	@Override
	public Map<String, Object> loadWeather(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT we_max_tmp,we_min_tmp,(SELECT we_name FROM common_weather we WHERE we.we_id = t.we_we_id) AS we_name,");
		sql.append(" (SELECT spi_city FROM t_base_shop_info spi WHERE spi_shop_code = we_shop_code AND companyid = t.companyid) AS city_name");
		sql.append(" FROM t_sell_weather t");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(params.get("shopCode"))){
			params.put("shopCodes", Arrays.asList(params.get("shopCode").toString().split(",")));
			sql.append(" AND we_shop_code IN(:shopCodes)");
		}
		sql.append(" AND we_date = :date");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
		} catch (Exception e) {
			return new HashMap<String, Object>();
		}
	}
	
}
